-- **************************************************************************
-- ** 创建者   : suning
-- ** 创建日期 : 20230131
-- ** 功能描述 ：最慢路由表
-- **************************************************************************
-- **************************** 修改日志 ************************************
-- **************************************************************************
-- ** 依赖表：
-- jms_dm.dm_route_whole_base_dt    --全量静态路由
-- **************************************************************************
-- *******************  CURRENT_VERSION ： V.1.1  ***************************
-- **************************************************************************
drop table jms_dm.dm_route_slowest;
create external table if not exists jms_dm.dm_route_slowest(
    in_from_code                             string                    comment'始发编码'
   ,update_date                              string                    comment'更新日期'
   ,in_from_provider_code                    string                    comment'始发省份编码'
   ,in_from_city_code                        string                    comment'始发市编码'
   ,in_from_area_code                        string                    comment'始发区县编码'
   ,out_to_provider_code                     string                    comment'目的省份'
   ,out_to_city_code                         string                    comment'目的市'
   ,out_to_area_code                         string                    comment'目的区县'
   ,out_to_code                              string                    comment'目的网点编码'
   ,in_branch_id                             string                    comment'集货id'
   ,main_id                                  string                    comment'干线全程id'
   ,out_branch_id                            string                    comment'散货id'
   ,in_collect_code                          string                    comment'始发集散点编码'
   ,in_to_code                               string                    comment'始发中心编码'
   ,start_center_code                        string                    comment'始发转运中心编码'
   ,search_type                              int                       comment'搜索类型'
   ,start_network_code                       string                    comment'始发网点编码'
   ,in_from_regional_code                    string                    comment'始发大区编码'
   ,in_from_regional_desc                    string                    comment'始发大区'
   ,in_from_financial_center_code            string                    comment'始发代理区编码'
   ,in_from_financial_center_desc            string                    comment'始发代理区'
   ,in_from_provider_desc                    string                    comment'始发省份'
   ,in_from_city_desc                        string                    comment'始发市'
   ,in_from_area_desc                        string                    comment'始发区县'
   ,in_collect_name                          string                    comment'始发集散点'
   ,in_from_name                             string                    comment'始发网点'
   ,in_to_name                               string                    comment'始发转运中心'
   ,in_edge_latest_warehousing               string                    comment'网点规划最晚入仓时间'
   ,in_edge_planned_departure                string                    comment'网点交件规划发车时间'
   ,in_edge_planned_arrival                  string                    comment'网点规划交件到达时间'
   ,in_edge2_planned_departure               string                    comment'集散点规划发车时间'
   ,in_edge2_planned_arrival                 string                    comment'集散点规划到达时间'
   ,branch_in_span_days                      int                       comment'交件耗时（天）'
   ,branch_in_total_time                     int                       comment'交件耗时（分）'
   ,is_main_route                            string                    comment'是否是主路由'
   ,transfer_type                            string                    comment'中转类型'
   ,num_of_transfer                          string                    comment'中转次数'
   ,whole_route                              string                    comment'路由环节'
   ,e1_center_flow                           string                    comment'流向1'
   ,e1_line_code                             string                    comment'流向1线路名称'
   ,e1_planned_departure                     string                    comment'流向1规划发出时间'
   ,e1_runtime                               int                       comment'流向1规划运行时长'
   ,e1_planned_arrival                       string                    comment'流向1规划到达时间'
   ,e1_e2_stop_time                          string                    comment'转-转衔接时长e1-e2'
   ,e2_center_flow                           string                    comment'流向2'
   ,e2_line_code                             string                    comment'流向2线路名称'
   ,e2_planned_departure                     string                    comment'流向2规划发出时间'
   ,e2_runtime                               int                       comment'流向2规划运行时长'
   ,e2_planned_arrival                       string                    comment'流向2规划到达时间'
   ,e2_e3_stop_time                          string                    comment'转-转衔接时长e2-e3'
   ,e3_center_flow                           string                    comment'流向3'
   ,e3_line_code                             string                    comment'流向3线路名称'
   ,e3_planned_departure                     string                    comment'流向3规划发出时间'
   ,e3_runtime                               int                       comment'流向3规划运行时长'
   ,e3_planned_arrival                       string                    comment'流向3规划到达时间'
   ,e3_e4_stop_time                          string                    comment'转-转衔接时长e3-e4'
   ,e4_center_flow                           string                    comment'流向4线路名称'
   ,e4_line_code                             string                    comment'流向4'
   ,e4_planned_departure                     string                    comment'流向4规划发出时间'
   ,e4_runtime                               int                       comment'流向4规划运行时长'
   ,e4_planned_arrival                       string                    comment'流向4规划到达时间'
   ,e4_e5_stop_time                          string                    comment'转-转衔接时长e4-e5'
   ,e5_center_flow                           string                    comment'流向5'
   ,e5_line_code                             string                    comment'流向5线路名称'
   ,e5_planned_departure                     string                    comment'流向5规划发出时间'
   ,e5_runtime                               int                       comment'流向5规划运行时长'
   ,e5_planned_arrival                       string                    comment'流向5规划到达时间'
   ,e5_e6_stop_time                          string                    comment'转-转衔接时长e2-e3'
   ,e6_center_flow                           string                    comment'流向6'
   ,e6_line_code                             string                    comment'流向6线路名称'
   ,e6_planned_departure                     string                    comment'流向6规划发出时间'
   ,e6_runtime                               int                       comment'流向6规划运行时长'
   ,e6_planned_arrival                       string                    comment'流向6规划到达时间'
   ,e6_e7_stop_time                          string                    comment'转-转衔接时长e6-e7'
   ,e1_end_code                              string                    comment'经停点1'
   ,e2_end_code                              string                    comment'经停点2'
   ,e3_end_code                              string                    comment'经停点3'
   ,e4_end_code                              string                    comment'经停点4'
   ,e5_end_code                              string                    comment'经停点5'
   ,e6_end_code                              string                    comment'经停点6'
   ,transfer_total_span_days                 string                    comment'中转跨天'
   ,transfer_total_time_use                  string                    comment'中转耗时（分）'
   ,main_total_span_days                     string                    comment'干线运输跨天'
   ,main_total_time_use                      string                    comment'干线运输耗时（分）'
   ,end_center                               string                    comment'目的中心'
   ,end_center_code                          string                    comment'目的中心编码'
   ,start_center                             string                    comment'始发中心'
   ,out_from_code                            string                    comment'目的转运中心编码'
   ,out_collect_code                         string                    comment'目的集散点编码'
   ,out_to_regional_desc                     string                    comment'目的大区'
   ,out_to_regional_code                     string                    comment'目的大区编码'
   ,out_to_financial_center_desc             string                    comment'目的代理区'
   ,out_to_financial_center_code             string                    comment'目的代理区编码'
   ,out_to_provider_desc                     string                    comment'目的省份'
   ,out_to_city_desc                         string                    comment'目的市'
   ,out_to_area_desc                         string                    comment'目的区县'
   ,out_collect_name                         string                    comment'目的集散点'
   ,out_to_name                              string                    comment'目的网点'
   ,out_from_name                            string                    comment'目的转运中心'
   ,out_has_collect                          string                    comment'线路是否有集散中心'
   ,out_edge_planned_departure               string                    comment'目的转运中心规划发车时间'
   ,out_edge_planned_arrival_time            string                    comment'集散点规划到车时间'
   ,out_edge2_planned_departure              string                    comment'集散点规划发车时间'
   ,out_edge2_planned_arrival_time           string                    comment'网点规划到车时间'
   ,out_edge2_latest_warehouse_time          string                    comment'集散规划出仓时间'
   ,out_edge_latest_warehouse_time           string                    comment'中心规划出仓时间'
   ,out_edge_span_days_sign                  string                    comment'集散派送加时'
   ,out_edge2_span_days_sign                 string                    comment'网点派送加时'
   ,out_edge_deadline_sign_time              string                    comment'集散点规划签收'
   ,out_edge2_deadline_sign_time             string                    comment'网点规划签收'
   ,branch_out_span_days                     int                       comment'派件耗时（天）'
   ,branch_out_total_time                    int                       comment'派件耗时（分）'
   ,effective_date                           string                    comment'生效日期'
   ,expiration_date                          string                    comment'失效日期'
   ,branch_in_center_stop_time               int                       comment'交转衔接时长'
   ,center_branch_out_stop_time              int                       comment'转散衔接时长'
   ,branch_in_center_span_days               int                       comment'交转衔接跨天'
   ,center_branch_out_span_days              int                       comment'转散衔接跨天'
   ,is_circuitous                            int                       comment'是否迂回'
   ,out_edge_span_days_arrive                int                       comment'集散跨越天数_到车的数据'
   ,out_edge2_span_days_arrive               int                       comment'网点跨越天数_到车的数据'
   ,in_nodes                                 int                       comment'集货节点数'
   ,out_nodes                                int                       comment'散货节点数'
   ,total_days_use                           string                    comment'全程时效(X日达)''
   ,total_time_use                           int                       comment'全程耗时(小时)''
   ,total_days_t                             string                    comment'全程T+时效(天)''
   ,total_nodes                              int                       comment'总节点数'
   ,working_days                             string                    comment'适用工作日'
   ,extra_in_collect_code                    string                    comment'始发集散点2编码'
   ,extra_in_collect_name                    string                    comment'始发集散点2'
   ,extra_in_line_name                       string                    comment'始发集散点2线路名称'
   ,extra_in_latest_warehousing              string                    comment'始发集散点2规划最晚到车时间'
   ,extra_in_planned_departure               string                    comment'始发集散点2规划发车时间'
   ,extra_in_planned_arrival                 string                    comment'始发集散点2规划到车时间'
   ,extra_out_collect_code                   string                    comment'目的集散点2编码'
   ,extra_out_collect_name                   string                    comment'目的集散点2'
   ,extra_out_line_name                      string                    comment'目的集散点2线路名称'
   ,extra_out_latest_warehousing             string                    comment'目的集散点2最晚到车时间'
   ,extra_out_planned_departure              string                    comment'目的集散点2规划发车时间'
   ,extra_out_planned_arrival                string                    comment'目的集散点2规划到车时间'
   ,in_network_line_name                     string                    comment'始发网点线路名'
   ,in_collect_line_name                     string                    comment'始发集散线路名'
   ,out_network_line_name                    string                    comment'目的网点线路名'
   ,out_collect_line_name                    string                    comment'目的集散线路名'
   ,town_plus_time                           string                    comment'交件乡镇加时'
   ,branch_in_ship_time                      string                    comment'交件运输耗时（分）'
   ,branch_in_ship_span_days                 string                    comment'交件运输耗时（天）'
   ,branch_out_ship_time                     string                    comment'派件运输耗时（分）'
   ,branch_out_ship_span_days                string                    comment'派件运输耗时（天）'
   ,all_line_name                            string                    comment'中转路由'
   ,out_town_plus_time                       int                       comment'派件乡镇加时'
   ,in_edge_run_time                         int                       comment'集货第一段耗时'
   ,in_edge_span_days                        int                       comment'集货第一段跨天'
   ,in_edge2_run_time                        int                       comment'集货第二段耗时'
   ,in_edge2_span_days                       int                       comment'集货第二段跨天'
   ,in_edge3_run_time                        int                       comment'集货第三段耗时'
   ,in_edge3_span_days                       int                       comment'集货第三段跨天'
   ,out_edge_run_time                        int                       comment'散货第一段耗时'
   ,out_edge_span_days                       int                       comment'散货第一段跨天'
   ,out_edge2_run_time                       int                       comment'散货第二段耗时'
   ,out_edge2_span_days                      int                       comment'散货第二段跨天'
   ,out_edge3_run_time                       int                       comment'散货第三段耗时'
   ,out_edge3_span_days                      int                       comment'散货第三段跨天'
   ,e1_span_days                             int                       comment'流向1规划跨天'
   ,e2_span_days                             int                       comment'流向2规划跨天'
   ,e3_span_days                             int                       comment'流向3规划跨天'
   ,e4_span_days                             int                       comment'流向4规划跨天'
   ,e5_span_days                             int                       comment'流向5规划跨天'
   ,e6_span_days                             int                       comment'流向6规划跨天'
   ,delivery_time                            string                    comment'"派件耗时,目的网点到车时间与签收时间的总耗时"'
   ,route_flow                               string                    comment'始发地-目的地流向'
   ,in_edge2_latest_warehousing              string                    comment'集散规划最晚到车时间'
   ,e1_latest_warehousing                    string                    comment'流向1规划最晚到车时间'
   ,e2_latest_warehousing                    string                    comment'流向2规划最晚到车时间'
   ,e3_latest_warehousing                    string                    comment'流向3规划最晚到车时间'
   ,e4_latest_arrival_time                   string                    comment'流向4规划最晚到车时间'
   ,e5_latest_arrival_time                   string                    comment'流向5规划最晚到车时间'
   ,e6_latest_arrival_time                   string                    comment'流向6规划最晚到车时间'
   ,start_taking_shift                       string                    comment'始发网点揽收班次'
   ,end_send_shift                           string                    comment'目的网点派仓班次'
   ,warehouse_end_time                       string                    comment'派仓派件结束时间'
   ,reserve_1                                string                    comment'reserve_1'
   ,reserve_2                                string                    comment'reserve_2'
   ,route_contain_main_line                  int                       comment'是否存在干线的线路 1是 0否'
   ,direct_route_type                        int                       comment'直发线路类型1网点-中心 2集散-中心 3中心-集散 4中心-网点 0没有来自干线的线路'
   ,in_line_day                              int                       comment'收件班次-支线衔接跨天'
   ,out_line_day                             int                       comment'派件班次-支线衔接跨天'
   ,in_ship_span_days                        int                       comment'"交件运输跨天, 网点的规划发车-始发中心到车时间的跨天数(不含集散停留时长)"'
   ,in_manage_region_code                    string                    comment'始发管理大区编码'
   ,in_manage_region_name                    string                    comment'始发管理大区名称'
   ,out_manage_region_code                   string                    comment'目的管理大区编码'
   ,out_manage_region_name                   string                    comment'目的管理大区名称'
   ,latest_warehousing_time_quantum          string                    comment'揽收时间段'
   ,branch_in_total_all_time                 int                       comment'集货总时效'
   ,main_total_all_time                      int                       comment'干线总时效'
   ,branch_out_total_all_time                int                       comment'散货总时效'
)comment '最慢路由表'
partitioned by (dt string comment '分区日期')
stored as parquet
location '/dw/hive/jms_dm.db/external/dm_route_slowest'
tblproperties (
'discover.partitions'='false',
'parquet.column.index.access'='true'
);

